import traceback
import pyautogui
import os
import pyperclip as clip
import json
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
import pypinyin
from bs4 import BeautifulSoup
import random
from jsjj.util.timeUtil import *
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.logUtil import *
from jsjj.util.mouseMoveUtil import *

# 所有京东的，都没有做断电续传的
# 运行模式: 日模式；周模式；月模式
runMode = '周模式'
modeName = '京东_纯自营_行业_店铺榜单'
# shopName = '★'
# 京东纯自营的大盘数据
# 1.朗程2016
# 2.时黛家居旗舰店
# 3.沃变
############网站的ajax中返回的
# 1.朗程
# 2.时黛家居（shidaijiaju）
# 3.沃变

deta = 35

downloadWeek = {'1': [526, 606], '2': [526, 638], '3': [526, 672],
                '4': [526, 705], '5': [526, 735]}

month = str(datetime.datetime.now().month - 1) + "月"
downloadMonth = {'1月': [511, 579], '2月': [589, 579], '3月': [671, 579],
                 '4月': [511, 628], '5月': [589, 628], '6月': [671, 628],
                 '7月': [511, 681], '8月': [589, 681], '9月': [671, 681],
                 '10月': [511, 733], '11月': [589, 733], '12月': [671, 733]}


def TopAndLeftMenu(engine, conn):
    pyautogui.FAILSAFE = False

    sleep(6)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')

    # 顶部行业
    pyautogui.moveTo(915, 139, 4)
    pyautogui.click()

    # 左边店铺榜单
    pyautogui.moveTo(123, 368, 4)
    pyautogui.click()

    # 这里可以稍微动态从ajax中得到店铺名称
    # filter textbox
    pyautogui.moveTo(1515, 171, 4)
    pyautogui.doubleClick()

    clip.copy('getBrandAndCategoryNew')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    # 选中第一个文件
    pyautogui.moveTo(1541, 326, 2)
    pyautogui.click()

    # 选中json区域
    pyautogui.moveTo(1830, 492, 2)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')

    # 读取剪切板内容json内容
    sleep(2)
    value = clip.paste()
    ajaxShopName = json.loads(value)
    shopName = ajaxShopName['content']['brand'][0]['name']

    # ******bug fix1:修正第一次缓存查询不出来
    deleteWholeWrongData(engine, shopName)
    # 点开菜单
    pyautogui.moveTo(412, 271, 2)
    pyautogui.click()

    pyautogui.moveTo(403, 461, 2)
    pyautogui.click()

    pyautogui.moveTo(393, 527, 2)
    pyautogui.click()

    # 选昨天的时间，只是为了避免第一次缓存
    pyautogui.moveTo(532, 527, 2)
    pyautogui.click()

    pyautogui.moveTo(1138, 800, 2)
    pyautogui.click()

    # ********

    # 纯自营
    pyautogui.moveTo(424, 272, 4)
    pyautogui.click()

    # 点击弹出下拉纯自营
    pyautogui.moveTo(840, 369, 4)
    pyautogui.click()

    # 选择下拉中的纯自营
    pyautogui.moveTo(811, 438, 4)
    pyautogui.click()

    if runMode == '周模式':
        # 按照周获取数据
        pyautogui.moveTo(408, 466, 4)
        pyautogui.click()

        pyautogui.moveTo(408, 686, 4)
        pyautogui.click()

        pyautogui.moveTo(529, 686, 4)
        pyautogui.click()

        # 默认抓取上周的数据
        currentWeekInThisMonth = getWeekOfMonth(datetime.datetime.now().year, datetime.datetime.now().month,
                                                datetime.datetime.now().day)

        # 动态计算出来，默认上周的数据
        currentWeekInThisMonth = currentWeekInThisMonth - 1
        pyautogui.moveTo(downloadWeek[str(currentWeekInThisMonth)][0], downloadWeek[str(currentWeekInThisMonth)][1], 4)
        pyautogui.click()

    elif runMode == '月模式':
        # 顶层按月查询
        pyautogui.moveTo(399, 466, 2)
        pyautogui.moveTo(376, 727, 2)

        # 选择到月份(6月)
        pyautogui.moveTo(downloadMonth[month][0], downloadMonth[month][1], 2)
        pyautogui.click()

    else:
        raise Exception("暂不支持日模式")

    # bug fix2，第一个类目要选择，并且就在这里将页面保存了，避免缓存的问题导致数据混乱
    # 重要步骤清空network
    pyautogui.moveTo(1542, 145, 2)
    pyautogui.click()

    pyautogui.moveTo(532, 370, 2)
    pyautogui.click()

    # 一级菜单
    pyautogui.moveTo(546, 407, 1)

    # 二级菜单
    pyautogui.moveTo(657, 407, 1)
    sleep(1)
    pyautogui.click()

    # 第一次查询（只是为了保存纯自营）
    pyautogui.moveTo(1138, 799, 4)
    pyautogui.click()

    # 保存第一页
    if shopName == '朗程':
        saveJson2Mysql('家具', '储物家具', '鞋架', conn, shopName)
    elif shopName == '时黛家居（shidaijiaju）':
        saveJson2Mysql('家具', '卧室家具', '床头柜', conn, shopName)
    elif shopName == '沃变':
        saveJson2Mysql('家具', '客厅家具', '电视柜', conn, shopName)

    return shopName


def LoopMenu(conn, shopName):
    # 店铺榜单有个特殊的地方，第一次打开页面的，他默认第一次不会再查询（★别人就没有做★）
    if shopName == '朗程':
        Lancheng(conn, shopName)
    elif shopName == '时黛家居（shidaijiaju）':
        Shidaijiaju(conn, shopName)
    elif shopName == '沃变':
        Wobian(conn, shopName)

    closeF12AtLastStep()


# 朗程2016
def Lancheng(conn, shopName):
    # 店铺榜单有个特殊的地方，第一次打开页面的，他默认第一次不会再查询（★别人就没有做★）
    # ★卧室家具(0)

    # ★客厅家具(0)

    # ★餐厅家具(0)

    # ★储物家具(2)
    for i in range(1, 2):
        # if i > 0:
        # 清除network
        pyautogui.moveTo(1541, 145, 4)
        pyautogui.click()

        # 类目textbox:弹出1
        pyautogui.moveTo(404, 274, 4)
        pyautogui.click()

        # 点开菜单弹出2
        pyautogui.moveTo(536, 368, 4)
        pyautogui.click()

        # 第一层
        pyautogui.moveTo(535, 407, 1)
        # 第二层
        pyautogui.moveTo(676, 407, 0.5)

        pyautogui.moveTo(676, 407 + i * deta, 0.5)
        sleep(1)
        pyautogui.click()

        if i == 0:
            saveJson2Mysql('家具', '储物家具', '鞋架', conn, shopName)
        elif i == 1:
            saveJson2Mysql('家具', '储物家具', '衣帽架', conn, shopName)

        sleep(3)

    # ★书房家具(1)
    for i in range(0, 1):
        # 清除network
        pyautogui.moveTo(1541, 145, 4)
        pyautogui.click()

        # 类目textbox:弹出1
        pyautogui.moveTo(404, 274, 4)
        pyautogui.click()

        # 点开菜单弹出2
        pyautogui.moveTo(536, 368, 4)
        pyautogui.click()

        # 第一层
        pyautogui.moveTo(535, 407 + 1 * deta, 1)
        # 第二层
        pyautogui.moveTo(676, 407 + 1 * deta, 0.5)

        pyautogui.moveTo(676, 407 + i * deta, 0.5)
        sleep(1)
        pyautogui.click()

        if i == 0:
            saveJson2Mysql('家具', '书房家具', '电脑桌', conn, shopName)

        sleep(3)


# 时黛家居旗舰店
def Shidaijiaju(conn, shopName):
    # 店铺榜单有个特殊的地方，第一次打开页面的，他默认第一次不会再查询（★别人就没有做★）
    # ★卧室家具(1)
    for i in range(1, 1):
        # if i > 0:
        # 清除network
        pyautogui.moveTo(1541, 145, 4)
        pyautogui.click()

        # 类目textbox:弹出1
        pyautogui.moveTo(404, 274, 4)
        pyautogui.click()

        # 点开菜单弹出2
        pyautogui.moveTo(536, 368, 4)
        pyautogui.click()

        # 第一层
        pyautogui.moveTo(535, 407 + 0 * deta, 1)
        # 第二层
        pyautogui.moveTo(676, 407 + 0 * deta, 0.5)

        pyautogui.moveTo(676, 407 + i * deta, 0.5)
        sleep(1)
        pyautogui.click()

        if i == 0:
            saveJson2Mysql('家具', '卧室家具', '床头柜', conn, shopName)

        sleep(3)

    # ★客厅家具(2)
    for i in range(0, 2):
        # 清除network
        pyautogui.moveTo(1541, 145, 4)
        pyautogui.click()

        # 类目textbox:弹出1
        pyautogui.moveTo(404, 274, 4)
        pyautogui.click()

        # 点开菜单弹出2
        pyautogui.moveTo(536, 368, 4)
        pyautogui.click()

        # 第一层
        pyautogui.moveTo(535, 407 + 1 * deta, 1)
        # 第二层
        pyautogui.moveTo(676, 407 + 1 * deta, 0.5)

        pyautogui.moveTo(676, 407 + i * deta, 0.5)
        sleep(1)
        pyautogui.click()

        if i == 0:
            saveJson2Mysql('家具', '客厅家具', '边桌/茶几', conn, shopName)
        elif i == 1:
            saveJson2Mysql('家具', '客厅家具', '鞋柜', conn, shopName)

        sleep(3)

    # ★餐厅家具(1)
    for i in range(0, 1):
        # 清除network
        pyautogui.moveTo(1541, 145, 4)
        pyautogui.click()

        # 类目textbox:弹出1
        pyautogui.moveTo(404, 274, 4)
        pyautogui.click()

        # 点开菜单弹出2
        pyautogui.moveTo(536, 368, 4)
        pyautogui.click()

        # 第一层
        pyautogui.moveTo(535, 407 + 2 * deta, 1)
        # 第二层
        pyautogui.moveTo(676, 407 + 2 * deta, 0.5)

        pyautogui.moveTo(676, 407 + i * deta, 0.5)
        sleep(1)
        pyautogui.click()

        if i == 0:
            saveJson2Mysql('家具', '餐厅家具', '餐边柜', conn, shopName)

        sleep(3)

    # ★储物家具(2)
    for i in range(0, 2):
        # 清除network
        pyautogui.moveTo(1541, 145, 4)
        pyautogui.click()

        # 类目textbox:弹出1
        pyautogui.moveTo(404, 274, 4)
        pyautogui.click()

        # 点开菜单弹出2
        pyautogui.moveTo(536, 368, 4)
        pyautogui.click()

        # 第一层
        pyautogui.moveTo(535, 407 + 3 * deta, 1)
        # 第二层
        pyautogui.moveTo(676, 407 + 3 * deta, 0.5)

        pyautogui.moveTo(676, 407 + i * deta, 0.5)
        sleep(1)
        pyautogui.click()

        if i == 0:
            saveJson2Mysql('家具', '储物家具', '储物/收纳用品', conn, shopName)
        elif i == 1:
            saveJson2Mysql('家具', '储物家具', '层架/置物架', conn, shopName)

        sleep(3)

    # ★书房家具(无)


# 沃变
def Wobian(conn, shopName):
    # 店铺榜单有个特殊的地方，第一次打开页面的，他默认第一次不会再查询（★别人就没有做★）
    # json移动菜单

    # ★卧室家具(0)

    # ★客厅家具(2)
    for i in range(1, 2):
        # if i > 0:
        # 清除network
        pyautogui.moveTo(1541, 145, 4)
        pyautogui.click()

        # 类目textbox:弹出1
        pyautogui.moveTo(404, 274, 4)
        pyautogui.click()

        # 点开菜单弹出2
        pyautogui.moveTo(536, 368, 4)
        pyautogui.click()

        # 第一层
        pyautogui.moveTo(535, 407 + 0 * deta, 1)
        # 第二层
        pyautogui.moveTo(676, 407 + 0 * deta, 0.5)

        pyautogui.moveTo(676, 407 + i * deta, 0.5)
        sleep(1)
        pyautogui.click()

        if i == 0:
            saveJson2Mysql('家具', '客厅家具', '电视柜', conn, shopName)
        elif i == 1:
            saveJson2Mysql('家具', '客厅家具', '鞋柜', conn, shopName)

        sleep(3)
    # ★餐厅家具(0)

    # ★储物家具(0)

    # ★书房家具(2)
    for i in range(0, 2):
        # 清除network
        pyautogui.moveTo(1541, 145, 4)
        pyautogui.click()

        # 类目textbox:弹出1
        pyautogui.moveTo(404, 274, 4)
        pyautogui.click()

        # 点开菜单弹出2
        pyautogui.moveTo(536, 368, 4)
        pyautogui.click()

        # 第一层
        pyautogui.moveTo(535, 407 + 1 * deta, 1)
        # 第二层
        pyautogui.moveTo(676, 407 + 1 * deta, 0.5)

        pyautogui.moveTo(676, 407 + i * deta, 0.5)
        sleep(1)
        pyautogui.click()

        if i == 0:
            saveJson2Mysql('家具', '书房家具', '书架', conn, shopName)
        elif i == 1:
            saveJson2Mysql('家具', '书房家具', '电脑桌', conn, shopName)

        sleep(3)


# 公用的保存到平台mysq中
def saveJson2Mysql(cat1, cat2, cat3, conn, shopName):
    # 点击确认查询
    pyautogui.moveTo(1134, 800, 4)
    pyautogui.click()

    # 点击filter textbox
    pyautogui.moveTo(1515, 171, 4)
    pyautogui.doubleClick()

    clip.copy('getShopListData')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    # 选中第一个文件
    pyautogui.moveTo(1541, 326, 2)
    pyautogui.click()

    # 选中json区域
    pyautogui.moveTo(1830, 492, 2)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')

    # 读取剪切板内容json内容
    sleep(2)
    value = clip.paste()
    jsonFromWeb1 = json.loads(value)

    df = pd.read_json(json.dumps(jsonFromWeb1['content']['data']))
    df['cat1'] = cat1
    df['cat2'] = cat2
    df['cat3'] = cat3
    df['插入时间'] = datetime.datetime.now()
    df['statDate'] = datetime.datetime.now()
    df['统计账号'] = shopName
    # 默认统计上周的数据
    df['统计时间周'] = str(datetime.datetime.now().isocalendar()[1] - 1)

    df['运行模式'] = runMode
    df['统计月'] = ''
    df['统计周'] = ''
    df['统计日'] = ''
    df['统计日周月'] = ''
    if runMode == '月模式':
        df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
        df['统计日周月'] = df['统计月']
    if runMode == '周模式':
        df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                      datetime.datetime.now().day)
        df['统计日周月'] = df['统计周']
    if runMode == '日模式':
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        df['统计日周月'] = df['统计日']
    # 解决可能出现的超时问题bugfix 2020.08.18
    conn.connection.connection.ping(reconnect=True)
    # 京东_纯自营_行业_店铺榜单
    df.to_sql(name='京东_纯自营_行业_店铺榜单', con=conn, if_exists='append', index=False)


def deleteWholeWrongData(engine, shopName):
    # 原则上都是某个模式下的某月，某天的数据全部删除，再重新跑
    strDayWeekMonth = ''
    if runMode == '月模式':
        strDayWeekMonth = str(datetime.datetime.now().month - 1) + "月"
    if runMode == '周模式':
        strDayWeekMonth = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,
                                            datetime.datetime.now().day)
    if runMode == '日模式':
        strDayWeekMonth = strDayWeekMonth(datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
    sql = "delete from " + modeName + " where 运行模式='" + runMode + "' and 统计日周月='" + strDayWeekMonth + "' and 统计账号='" + shopName + "'"
    engine.execute(sql)


def deleteDuplicationData(engine):
    # 这里有特殊的逻辑，不同账号，可能有相同的类目，同一天的数据，是一模一样的，因此我们需要去除重复数据的
    sql = 'delete from a                                                              '
    sql += '    using 京东_纯自营_行业_店铺榜单 as a, 京东_纯自营_行业_店铺榜单 as b   '
    sql += '    where (a.id < b.id)                                                    '
    sql += '    and (a.cat3 = b.cat3 and a.statDate=b.statDate and a.rank=b.rank and a.统计账号=b.统计账号 )     '
    engine.execute(sql)


def deleteDuplicationDataDifferentShop(engine):
    # 不同店铺之间，会重复拉三级类目的，因此要删除重复的三级类目
    # 京东_纯自营_行业_店铺榜单
    sql = 'delete from a                                                              '
    sql += '    using 京东_纯自营_行业_店铺榜单 as a, 京东_纯自营_行业_店铺榜单 as b   '
    sql += '    where (a.id < b.id)                                                    '
    sql += '    and (a.cat3 = b.cat3 and a.运行模式=b.运行模式 and a.统计日周月=b.统计日周月 and a.Rank=b.Rank )                        '
    engine.execute(sql)


def executeCatchWeb(engine, conn):
    try:
        shopName = TopAndLeftMenu(engine, conn)
        LogTaskAndMachine('京东_纯自营_行业_店铺榜单', engine, conn, shopName, runMode)
        LoopMenu(conn, shopName)
        deleteDuplicationData(engine)
        deleteDuplicationDataDifferentShop(engine)
    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    # 连接database
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
